﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;

namespace Common.SQL
{
    /// <summary>
    /// 翻页查询抽象类
    /// </summary>
    public abstract class SQLQueryPage
    {
        static log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        protected IDbConnection _conn = null;
        protected string _strSQL = null;
        protected string _strCountSQL = null;
        protected int _iPage = 0;
        protected int _iPageSize = 20;
        protected int _iTotalCount = 0;
        protected int _iTotalPage = 1;
        protected IDbDataParameter[] _parameters = null;

        /// <summary>
        /// 数据库连接
        /// </summary>
        public IDbConnection Connection
        {
            set { _conn = value; }
            get { return _conn; }
        }

        /// <summary>
        /// 设置查询SQL
        /// </summary>
        protected string  SQL
        {
            set { _strSQL = value; }
            get { return _strSQL; }
        }

        /// <summary>
        /// 设置获得结果数的SQL
        /// </summary>
        protected string CountSQL
        {
            set { _strCountSQL = value; }
            get { return _strCountSQL; }
        }

        /// <summary>
        /// 设置当前所在页
        /// </summary>
        public int CurPage
        {
            set { _iPage = value; }
            get { return _iPage; }
        }

        /// <summary>
        /// 设置每页最大结果数
        /// </summary>
        public int PageSize
        {
            set { _iPageSize = value; }
            get { return _iPageSize; }
        }

        /// <summary>
        /// 返回总的结果数
        /// </summary>
        public int TotalCount
        {
            //set { _iTotalCount = value; }
            get { return _iTotalCount; }
        }

        /// <summary>
        /// 返回总页数
        /// </summary>
        public int TotalPage
        {
            //set { _iTotalPage = value; }
            get { return _iTotalPage; }
        }

        /// <summary>
        /// 通过CountSQL查询数据库的结果个数
        /// </summary>
        /// <returns></returns>
        protected int QueryRecordNumber()
        {
            int iCount = 0;
            if (_strCountSQL == null)
            {
                logger.Error("QueryRecordNumber: CountSQL is null");
                return 0;
            }

            iCount = Convert.ToInt32(SQLHelper.ExecuteScalar(_conn, CommandType.Text, _strCountSQL, null));

            return iCount;
        }

        /// <summary>
        /// 填充数据
        /// </summary>
        /// <returns></returns>
        protected abstract object[] FillData(IDataReader dataReader);

        

        protected IDbDataParameter[] Parameters
        {
            set { this._parameters = value; }
        }

        /// <summary>
        /// 查询结果
        /// </summary>
        /// <returns></returns>
        public object[] GetPage()
        {
            IDataReader reader = null;
            object[] objects = null;

            //判断连接
            if (_conn == null)  //如果没有连接，则创建
            {
                _conn = DBPool.Instance().GetConnection();

                if (_conn == null)
                {
                    logger.Error("GetPage: Connection is null");
                    return null;
                }
            }

            try
            {
                //查询结果数
                _iTotalCount = QueryRecordNumber();

                //计算分页
                if (_iPageSize <= 0)
                    _iPageSize = 1;

                float tmpResult = (float)_iTotalCount / _iPageSize;
                _iTotalPage = (int)tmpResult;

                if (tmpResult > _iTotalPage)
                {
                    _iTotalPage++;
                }

                if (_iPage < 1)
                {
                    _iPage = 1;
                }
                else if (_iPage > _iTotalPage)
                {
                    objects = null;// 如果传入大于总页数的页码，返回空
                    return null;
                    //_iPage = _iTotalPage;
                }

                if (_iPage < 1) //当前页不能小于0，否则mysql的limit语法会有错误
                    _iPage = 1;

                //输出调试SQL
                logger.Debug(_strSQL + " limit " + (_iPage - 1) * _iPageSize + "," + _iPageSize);

                //查询数据
                reader = SQLHelper.QueryReader(_conn, CommandType.Text, _strSQL + " limit " + (_iPage - 1) * _iPageSize + "," + _iPageSize, _parameters);
               
                objects = FillData(reader);
            }
            catch (Exception e)
            {
                logger.Error("GetPage: " + e);
            }
            finally
            {
                //关闭reader
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();
                }

                //关闭连接
                DBPool.Instance().FreeConnection(_conn);
            }

            //填充数据
            return objects;
        }
    }
}
